package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo6 {
    public static void main(String[] args) {
        try(
            Connection conn = DBUtil.getConnection();
        ){
            // 创建statement对象
            Statement statement = conn.createStatement();

            /*
             *  由于我自己的数据库的表太少，所以这里创建了一个后，换为教学的数据库啦
             */
            // 创建订单表
            String sql = "create table orders(" +
                    "id int primary key auto_increment," +
                    "order_name varchar(20) not null," +
                    "order_money double not null," +
                    "order_date BIGINT not null," +
                    "user_id int not null"+
                    ")";
            // statement.execute(sql);

            // 向订单表中插入数据
            String sql1 = "insert into orders(order_name,order_money,order_date,user_id) values" +
                    "('java',1000,1234567890,3)," +
                    "('c++',200,178293012,2)";
            // System.out.println(sql1);
            // statement.execute(sql1);

            // 执行关联查询
            String sql2 = "SELECT u.username,o.order_name " +
                    "FROM userinfo u " +
                    "JOIN orders o ON o.user_id = u.id";
            System.out.println(sql2);
            statement.execute(sql2);
            // 遍历结果集
            ResultSet resultSet = statement.getResultSet();

            // 遍历结果集
            while (resultSet.next()){
                String name = resultSet.getString("u.username");
                String order_name = resultSet.getString("o.order_name");
                System.out.println(name + ":" + order_name);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
